import random
from openpyxl import Workbook, load_workbook
import pymysql
from datetime import datetime, timedelta


def generate_excel_data():
    """生成Excel测试数据"""
    wb = Workbook()
    ws = wb.active
    ws.title = "UserData"

    # 写入表头
    headers = [
        "id", "idcard", "username", "realname", "pwd",
        "telphone", "email", "age", "sex", "address",
        "hiredate", "sal", "job", "company"
    ]
    ws.append(headers)

    # 生成随机数据
    for i in range(1, 2001):
        row_data = [
            i,  # id
            f"510{random.randint(1000000000000, 9999999999999)}",  # idcard
            f"user_{i}",  # username
            random.choice(["张", "李", "王", "赵", "刘"]) + random.choice(["伟", "芳", "娜", "秀英", "强"]),  # realname
            f"pass_{random.randint(1000, 9999)}",  # pwd
            f"1{random.randint(30, 39)}{random.randint(10000000, 99999999)}",  # telphone
            f"user{i}@example.com",  # email
            random.randint(18, 65),  # age
            random.choice(["男", "女"]),  # sex
            random.choice(["北京", "上海", "广州", "深圳", "成都"]) + "市某区某街道" + str(
                random.randint(1, 999)) + "号",  # address
            (datetime.now() - timedelta(days=random.randint(1, 3650))).strftime("%Y-%m-%d"),  # hiredate
            round(random.uniform(3000, 30000), 2),  # sal
            random.choice(["工程师", "经理", "销售", "设计师", "会计", "HR"]),  # job
            random.choice(["阿里巴巴", "腾讯", "百度", "京东", "字节跳动", "华为"]) + random.choice(
                ["有限公司", "集团", "科技"])  # company
        ]
        ws.append(row_data)

    wb.save("user_data.xlsx")
    print("Excel文件创建完成，已写入2000条数据")


def process_data_to_mysql():
    """处理数据到MySQL"""
    try:
        # 建立数据库连接
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            database='testdb',
            charset='utf8'
        )

        with conn.cursor() as cursor:
            # 创建表（如果不存在）
            create_table_sql = """
            CREATE TABLE IF NOT EXISTS `user_info` (
              `id` int(11),
              `idcard` varchar(50),
              `username` varchar(50),
              `realname` varchar(50),
              `pwd` varchar(50),
              `telphone` varchar(12),
              `email` varchar(100),
              `age` int(11),
              `sex` varchar(20),
              `address` varchar(200),
              `hiredate` date,
              `sal` double(9,2),
              `job` varchar(100),
              `company` varchar(100)
            )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
            cursor.execute(create_table_sql)
            conn.commit()
            print("表创建/验证完成")

            # 读取Excel
            wb = load_workbook("user_data.xlsx")
            ws = wb.active
            rows = list(ws.iter_rows(min_row=2, values_only=True))  # 转换为list确认数量
            print(f"Excel中读取到 {len(rows)} 条数据")

            # 批量插入
            insert_sql = """INSERT INTO user_info VALUES (%s, %s, %s, %s, %s, %s, %s, 
                                      %s, %s, %s, %s, %s, %s, %s)"""

            batch_size = 100
            for i in range(0, len(rows), batch_size):
                batch = rows[i:i + batch_size]
                cursor.executemany(insert_sql, batch)
                conn.commit()
                print(f"已提交 {i + len(batch)}/{len(rows)} 条数据", end="\r")

            print(f"\n成功插入 {len(rows)} 条数据")

    except Exception as e:
        conn.rollback()
        print(f"\n错误: {e}")
    finally:
        conn.close()


if __name__ == "__main__":
    # 生成Excel测试数据
    generate_excel_data()

    # 处理数据到MySQL（单次连接完成所有操作）
    process_data_to_mysql()